package com.baiyang.generator.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.baiyang.generator.conf.DatabaseConfig;
import com.baiyang.generator.entity.ColumnInfo;
import com.baiyang.generator.utils.StringUtil;

/**
 * @explain 数据库相关操作
 * @author XVX
 */
public class InitDB {

    /**
     * 数据库连接
     */
    private Connection connection;
    
    public Connection getConnection() {
		return connection;
	}
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	/**
     * 初始化数据库连接
     *
     * @return 连接是否建立成功
     */
    public boolean initConnection() {
        try {
        	DatabaseConfig config = DatabaseConfig.build();
        	if(config.isNotEmpty()) {
        		return false;
        	}
        	Class.forName(config.getDriver());
            Properties properties = new Properties();
            properties.put("user", config.getUserName());
            properties.put("password", config.getPassword());
            properties.setProperty("remarks", "true");
            properties.setProperty("useInformationSchema", "true");
            properties.setProperty("nullCatalogMeansCurrent", "true");
            connection = DriverManager.getConnection(config.getUrl(), properties);
            return true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    /**
     * 获取列信息
     *
     * @param tableName
     * @param primaryKey
     * @param tableRemark
     * @return
     * @throws Exception
     */
    public List<ColumnInfo> getColumnInfos(String tableName, String primaryKey, String tableRemark) throws Exception {
        // 获取列信息
        List<ColumnInfo> columnInfos = new ArrayList<ColumnInfo>();
        ResultSet columnResultSet = connection.getMetaData().getColumns(getCatalog(connection),
                getSchema(connection), tableName, "%");
        while (columnResultSet.next()) {
            boolean isPrimaryKey;
            if (columnResultSet.getString("COLUMN_NAME").equals(primaryKey)) {
                isPrimaryKey = true;
            } else {
                isPrimaryKey = false;
            }
            ColumnInfo info = new ColumnInfo(columnResultSet.getString("COLUMN_NAME"), columnResultSet.getInt("DATA_TYPE"),
                    StringUtil.isEmpty(columnResultSet.getString("REMARKS")) ? "Unknown" : columnResultSet.getString("REMARKS"),
                    tableRemark, isPrimaryKey,columnResultSet.getString("NULLABLE"));
            columnInfos.add(info);
        }
        columnResultSet.close();
        if (columnInfos.size() == 0) {
            closeConnection();
            throw new Exception("Can not find column information from table:" + tableName);
        }
        return columnInfos;
    }
    /**
     * 获取主键
     *
     * @param tableName
     * @return 主键名称
     * @throws SQLException
     */
    public String getPrimaryKey(String tableName) throws SQLException {
        // 获取主键
        ResultSet keyResultSet = connection.getMetaData().getPrimaryKeys(getCatalog(connection),
                getSchema(connection), tableName);
        String primaryKey = null;
        if (keyResultSet.next()) {
            primaryKey = keyResultSet.getObject(4).toString();
        }
        keyResultSet.close();
        return primaryKey;
    }
    /**
     * 获取表注释
     *
     * @param tableName
     * @return
     * @throws SQLException
     */
    public String getTableRemark(String tableName) throws SQLException {
        // 获取表注释
        String tableRemark = null;
        if (connection.getMetaData().getURL().contains("sqlserver")) { // SQLServer
        } else { // Oracle & MySQL
            ResultSet tableResultSet = connection.getMetaData().getTables(getCatalog(connection),
                    getSchema(connection), tableName, new String[]{"TABLE"});
            if (tableResultSet.next()) {
                tableRemark = StringUtil.isEmpty(tableResultSet.getString("REMARKS")) ?
                        "Unknown Table" : tableResultSet.getString("REMARKS");
            }
            tableResultSet.close();
        }
        return tableRemark;
    }
    /**
     * 获取schema
     *
     * @param connection 数据库连接
     * @return schema
     * @throws SQLException
     */
    public static String getSchema(Connection connection) throws SQLException {
        String url = connection.getMetaData().getURL();
        if (url.contains("mysql")) {
            if (url.contains("?")) {
                url = url.replace("jdbc:mysql://", "");
                return url.substring(url.indexOf("/") + 1, url.lastIndexOf("?"));
            } else {
                return url.substring(url.lastIndexOf("/") + 1);
            }
        } else if (url.contains("oracle")) {
            return connection.getMetaData().getUserName();
        } else if (url.contains("sqlserver")) {
            return connection.getSchema();
        }
        return null;
    }
    
    
    /**
     * 获取catalog
     *
     * @param connection 数据库连接
     * @return catalog
     * @throws SQLException
     */
    public static String getCatalog(Connection connection) throws SQLException {
        String url = connection.getMetaData().getURL();
        if (url.contains("mysql")) {
            return null;
        } else if (url.contains("oracle")) {
            return null;
        } else if (url.contains("sqlserver")) {
            return url.substring(url.lastIndexOf("=") + 1);
        }
        return null;
    }

    /**
     * 关闭数据库连接
     * @throws SQLException
     */
    public void closeConnection() throws SQLException {
        if (!connection.isClosed()) {
            connection.close();
        }
    }
}
